package stock.data;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

/**
 * Operate RDB
 * @author i062843
 *
 */
public class DatabaseImpl {

	private static Logger logger = LogManager.getLogger(DatabaseImpl.class
			.getName());
	private static SimpleDateFormat DATE_FORMAT = new SimpleDateFormat(
			"yyyy/mm/dd");

	private ConnectionPool connPool = null;

	/*
	 * private static int UPDATE_BATCH_SIZE = 100;
	 * 
	 * private int batchAmount = 0;
	 */
	public void initDatabase(String jdbcDriver, String dbUrl,
			String dbUsername, String dbPassword) throws SQLException {
		// Connection conn = getConnection(jdbcDriver, dbUrl, dbUsername,
		// dbPassword);
		initConnectionPool(jdbcDriver, dbUrl, dbUsername, dbPassword);
		// TODO: execute DDL, create database, tables
		truncateTables();
		// populateTables();
	}

	private void initConnectionPool(String jdbcDriver, String dbUrl,
			String dbUsername, String dbPassword) throws SQLException {
		if (connPool == null) {
			connPool = new ConnectionPool(jdbcDriver, dbUrl, dbUsername,
					dbPassword);
		}

	}

	protected void truncateTables() throws SQLException {

		Connection conn = connPool.getConnection();
		Statement statement = conn.createStatement();
		String truncate_security_info = String.format(
				DatabaseMetadata.TRUNCATE_TABLE,
				DatabaseMetadata.SECURITY_INFO_TABLE);
		logger.info("truncate table security_info : %s", truncate_security_info);
		;
		statement.executeUpdate(truncate_security_info);
		String truncate_security_data = String.format(
				DatabaseMetadata.TRUNCATE_TABLE,
				DatabaseMetadata.SECURITY_DATA_TABLE);
		logger.info("truncate table security_data : %s", truncate_security_data);
		;
		statement.executeUpdate(truncate_security_data);

		statement.close();
		connPool.returnConnection(conn);
	}

	public void populateTable(String securityId, String securityName,
			String dataFileFullName) throws SQLException, IOException, ParseException {
		populateSecurityInfo(securityId, securityName);
		populateSecurityData(securityId, dataFileFullName);
	}

	private void populateSecurityData(String securityId, String dataFileFullName) throws IOException, SQLException, ParseException {
		logger.info("populate security info table");
		CSVUtil util = new CSVUtil(securityId, dataFileFullName);
		util.run(this);
	}

	private Connection conn = null;
	private PreparedStatement ps = null;
	private int count = 0;
	private static int BATCH_SIZE = 100;

	public void addPopulateSecurityDataBatch(String securityId,
			String date, String open, String high, String low, String close,
			String vol, String amount) throws SQLException, ParseException {
		if (conn == null) {
			conn = connPool.getConnection();
			ps = conn.prepareStatement(DatabaseMetadata.INSERT_SECURITY_DATA);
		}

		ps.setInt(1, Integer.parseInt(securityId));
		ps.setDate(2, new java.sql.Date(DATE_FORMAT.parse(date).getTime()));
		ps.setString(3, open);
		ps.setString(4, high);
		ps.setString(5, low);
		ps.setString(6, close);
		ps.setInt(7, Integer.parseInt(vol));
		ps.setLong(8, Long.parseLong(amount));

		ps.addBatch();

		if (++count % BATCH_SIZE == 0) {
			ps.executeBatch();
		}
	}

	public void executePopulateSecurityDataBatch() throws SQLException {
		if (ps != null) {
			ps.executeBatch();
		}
		
		//clean up
		ps.close();
		connPool.returnConnection(conn);
		ps = null;
		conn = null;
		count = 0;
	}

	private void populateSecurityInfo(String securityId, String securityName)
			throws SQLException {
		logger.info("populate security info table");
		Connection conn = connPool.getConnection();
		PreparedStatement preparedStatement = conn
				.prepareStatement(DatabaseMetadata.INSERT_SECURITY_INFO);
		preparedStatement.setInt(1, Integer.parseInt(securityId));
		preparedStatement.setString(2, securityName);
		// execute insert SQL stetement
		preparedStatement.executeUpdate();
		preparedStatement.close();
		connPool.returnConnection(conn);
	}

}
